-- 查询顾客的购买明细及月购买总额

SELECT *, 
SUM(cost) OVER(PARTITION BY name,month(orderdate))  
FROM business;


-- 上述的场景,要将cost按照日期进行累加
SELECT *, 
SUM(cost) OVER(PARTITION BY name,month(orderdate) ORDER BY orderdate
ROWs BETWEEN CURRENT ROW PRECEDING AND 1 FOLLOWING
) as sum_of_cost 
FROM business WHERE name = 'mart';

-- 查询顾客上次的购买时间 LAG(col,n,'9999-99-99')
SELECT name,orderdate, LAG(orderdate,1,'0000-00-00') OVER(PARTITION BY name ORDER BY orderdate) 
FROM business;

-- LEAD(col,n)

-- 查询前20%时间的订单信息  ntile(5) 分成五份  sorted = 1 拿第一份
select * from (
    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business
) t
where sorted = 1;

-- 每个顾客的订单金额的TOP2 
SELECT name,orderdate,cost,b.pk
FROM
(
    SELECT *,ROW_NUMBER() OVER(PARTITION BY name ORDER BY cost DESC) as pk
     FROM business 
) b WHERE b.pk <=2;



--  ROW_NUMBER 


